<?php
use Migrations\AbstractMigration;

class POCOR7940 extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function up()
    {
        // Backup affected tables
        $this->execute('CREATE TABLE `zz_7940_report_queries` LIKE `report_queries`');
        $this->execute('INSERT INTO `zz_7940_report_queries` SELECT * FROM `report_queries`');

        $this->execute('CREATE TABLE `zz_7940_summary_area_institution_grade_attendances` LIKE `summary_area_institution_grade_attendances`');
        $this->execute('INSERT INTO `zz_7940_summary_area_institution_grade_attendances` SELECT * FROM `summary_area_institution_grade_attendances`');
    
        // TRUNCATE the summary_area_institution_grade_attendances table since the attendance captured until this point are not captured correctly
        $this->execute('TRUNCATE TABLE summary_area_institution_grade_attendances;');
        
        // UPDATE the query used to populate the summary table 
        $this->execute('UPDATE `report_queries` SET `query_sql` = "INSERT INTO `summary_area_institution_grade_attendances`(`academic_period_id`, `academic_period_name`, `area_id`, `area_code`, `area_name`, `institution_id`, `institution_code`, `institution_name`, `education_grade_id`, `education_grade_code`, `education_grade_name`, `attendance_date`, `marked_classes` , `total_classes`, `female_count`, `male_count`, `total_count`, `present_female_count`, `present_male_count`, `present_total_count`, `absent_female_count`, `absent_male_count`, `absent_total_count`, `late_female_count`, `late_male_count`, `late_total_count`, `created`) SELECT academic_periods.id ,academic_periods.name ,areas.id ,areas.code ,areas.name ,institutions.id ,institutions.code ,institutions.name ,education_grades.id ,education_grades.code ,education_grades.name ,DATE(CONVERT_TZ(NOW(), @@session.time_zone, time_zone_info.value)) attendance_date ,0 ,COUNT(DISTINCT(institution_class_students.institution_class_id)) total_classes ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 1 THEN institution_class_students.student_id END)) male_count ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 2 THEN institution_class_students.student_id END)) female_count ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 1 THEN institution_class_students.student_id END)) + COUNT(DISTINCT(CASE WHEN security_users.gender_id = 2 THEN institution_class_students.student_id END)) total_students ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,CONVERT_TZ(NOW(), @@session.time_zone, time_zone_info.value) created_at FROM institution_class_students INNER JOIN security_users ON security_users.id = institution_class_students.student_id INNER JOIN institutions ON institutions.id = institution_class_students.institution_id INNER JOIN areas ON areas.id = institutions.area_id INNER JOIN education_grades ON education_grades.id = institution_class_students.education_grade_id INNER JOIN academic_periods ON academic_periods.id = institution_class_students.academic_period_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code = \'time_zone\') time_zone_info WHERE academic_periods.current = 1 AND institution_class_students.student_status_id = 1 GROUP BY education_grades.id ,institutions.id ,academic_periods.id ,attendance_date ORDER BY institutions.id ASC;" WHERE `report_queries`.`name` = "summary_area_institution_grade_attendances";');
        
        // DROP all triggers (including the newly developed triggers that were added during internal testing)
        $this->execute('DROP TRIGGER IF EXISTS trigger_institution_student_absence_details_insert;');
        $this->execute('DROP TRIGGER IF EXISTS trigger_institution_student_absence_details_delete;');
        $this->execute('DROP TRIGGER IF EXISTS trigger_student_attendance_marked_records;');
        $this->execute('DROP TRIGGER IF EXISTS trigger_summary_area_institution_grade_attendances_update;');
        $this->execute('DROP TRIGGER IF EXISTS trigger_student_attendance_marked_records_insert;');
        $this->execute('DROP TRIGGER IF EXISTS trigger_institution_student_absence_details_update;');

        // Create TRIGGER to count present students upon INSERT operation in student_attendance_marked_records
        $this->execute('CREATE TRIGGER trigger_student_attendance_marked_records_insert AFTER INSERT ON student_attendance_marked_records FOR EACH ROW BEGIN UPDATE summary_area_institution_grade_attendances INNER JOIN( SELECT COUNT(DISTINCT(attendance_details.institution_class_id)) marked_classes_counter ,SUM(class_counter.present_female_count) present_female_count ,SUM(class_counter.present_male_count) present_male_count FROM ( SELECT student_attendance_marked_records.academic_period_id ,student_attendance_marked_records.institution_id ,student_attendance_marked_records.education_grade_id ,student_attendance_marked_records.institution_class_id FROM student_attendance_marked_records WHERE student_attendance_marked_records.academic_period_id = NEW.academic_period_id AND student_attendance_marked_records.institution_id = NEW.institution_id AND student_attendance_marked_records.education_grade_id = NEW.education_grade_id AND student_attendance_marked_records.date = NEW.date AND student_attendance_marked_records.no_scheduled_class != 1 GROUP BY student_attendance_marked_records.academic_period_id ,student_attendance_marked_records.institution_id ,student_attendance_marked_records.education_grade_id ,student_attendance_marked_records.institution_class_id) attendance_details INNER JOIN ( SELECT institution_class_students.education_grade_id ,institution_class_students.institution_id ,institution_class_students.academic_period_id ,institution_class_students.institution_class_id ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 1 THEN institution_class_students.student_id END)) present_male_count ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 2 THEN institution_class_students.student_id END)) present_female_count FROM institution_class_students INNER JOIN security_users ON security_users.id = institution_class_students.student_id INNER JOIN academic_periods ON academic_periods.id = institution_class_students.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND institution_class_students.institution_id = NEW.institution_id AND institution_class_students.education_grade_id = NEW.education_grade_id AND institution_class_students.student_status_id = 1 GROUP BY institution_class_students.education_grade_id ,institution_class_students.institution_id ,institution_class_students.academic_period_id ,institution_class_students.institution_class_id ) class_counter ON class_counter.education_grade_id = attendance_details.education_grade_id AND class_counter.institution_id = attendance_details.institution_id AND class_counter.institution_class_id = attendance_details.institution_class_id AND class_counter.academic_period_id = attendance_details.academic_period_id GROUP BY attendance_details.academic_period_id ,attendance_details.institution_id ,attendance_details.education_grade_id ) attendance_info SET summary_area_institution_grade_attendances.present_female_count = CASE WHEN attendance_info.present_female_count > summary_area_institution_grade_attendances.present_female_count THEN attendance_info.present_female_count - summary_area_institution_grade_attendances.absent_female_count ELSE summary_area_institution_grade_attendances.present_female_count END ,summary_area_institution_grade_attendances.present_male_count = CASE WHEN attendance_info.present_male_count > summary_area_institution_grade_attendances.present_male_count THEN attendance_info.present_male_count - summary_area_institution_grade_attendances.absent_male_count ELSE summary_area_institution_grade_attendances.present_male_count END ,summary_area_institution_grade_attendances.marked_classes = CASE WHEN attendance_info.marked_classes_counter > summary_area_institution_grade_attendances.marked_classes THEN attendance_info.marked_classes_counter ELSE summary_area_institution_grade_attendances.marked_classes END WHERE summary_area_institution_grade_attendances.attendance_date = NEW.date AND summary_area_institution_grade_attendances.institution_id = NEW.institution_id AND summary_area_institution_grade_attendances.education_grade_id = NEW.education_grade_id AND summary_area_institution_grade_attendances.academic_period_id = NEW.academic_period_id; END; ');

        // Create TRIGGER to count absences and late upon INSERT operation in institution_student_absence_details
        $this->execute('CREATE TRIGGER trigger_institution_student_absence_details_insert AFTER INSERT ON institution_student_absence_details FOR EACH ROW BEGIN UPDATE summary_area_institution_grade_attendances SET summary_area_institution_grade_attendances.present_female_count = CASE WHEN NEW.absence_type_id != 3 AND( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND student_mark_type_status_grades.education_grade_id = NEW.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = NEW.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 2 AND institution_subject_students.education_grade_id = NEW.education_grade_id AND institution_subject_students.institution_id = NEW.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ) > summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.present_female_count - 1 ELSE summary_area_institution_grade_attendances.present_female_count END ,summary_area_institution_grade_attendances.present_male_count = CASE WHEN NEW.absence_type_id != 3 AND ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND student_mark_type_status_grades.education_grade_id = NEW.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = NEW.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 1 AND institution_subject_students.education_grade_id = NEW.education_grade_id AND institution_subject_students.institution_id = NEW.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ) > summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.present_male_count - 1 ELSE summary_area_institution_grade_attendances.present_male_count END ,summary_area_institution_grade_attendances.absent_female_count = CASE WHEN NEW.absence_type_id != 3 AND ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND student_mark_type_status_grades.education_grade_id = NEW.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = NEW.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 2 AND institution_subject_students.education_grade_id = NEW.education_grade_id AND institution_subject_students.institution_id = NEW.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ) > summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.absent_female_count + 1 ELSE summary_area_institution_grade_attendances.absent_female_count END ,summary_area_institution_grade_attendances.absent_male_count = CASE WHEN NEW.absence_type_id != 3 AND ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND student_mark_type_status_grades.education_grade_id = NEW.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = NEW.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 1 AND institution_subject_students.education_grade_id = NEW.education_grade_id AND institution_subject_students.institution_id = NEW.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ) > summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.absent_male_count + 1 ELSE summary_area_institution_grade_attendances.absent_male_count END ,late_female_count = CASE WHEN NEW.absence_type_id = 3 AND ( SELECT COUNT(DISTINCT(institution_student_absence_details.student_id)) late_counter FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id WHERE institution_student_absence_details.academic_period_id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id = NEW.absence_type_id AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ) > late_female_count THEN late_female_count + 1 ELSE late_female_count END ,late_male_count = CASE WHEN NEW.absence_type_id = 3 AND ( SELECT COUNT(DISTINCT(institution_student_absence_details.student_id)) late_counter FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id WHERE institution_student_absence_details.academic_period_id = NEW.academic_period_id AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.absence_type_id = NEW.absence_type_id AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ) > late_male_count THEN late_male_count + 1 ELSE late_male_count END WHERE summary_area_institution_grade_attendances.attendance_date = NEW.date AND summary_area_institution_grade_attendances.institution_id = NEW.institution_id AND summary_area_institution_grade_attendances.education_grade_id = NEW.education_grade_id AND summary_area_institution_grade_attendances.academic_period_id = NEW.academic_period_id; END; ');

        // Create TRIGGER to count absences and late upon DELETE operation in institution_student_absence_details
        $this->execute('CREATE TRIGGER trigger_institution_student_absence_details_delete AFTER DELETE ON institution_student_absence_details FOR EACH ROW BEGIN UPDATE summary_area_institution_grade_attendances SET summary_area_institution_grade_attendances.present_female_count = CASE WHEN OLD.absence_type_id != 3 AND COALESCE( ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = OLD.academic_period_id AND student_mark_type_status_grades.education_grade_id = OLD.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = OLD.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 2 AND institution_subject_students.education_grade_id = OLD.education_grade_id AND institution_subject_students.institution_id = OLD.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ), 0 ) < summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.present_female_count + 1 ELSE summary_area_institution_grade_attendances.present_female_count END ,summary_area_institution_grade_attendances.present_male_count = CASE WHEN OLD.absence_type_id != 3 AND COALESCE ( ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = OLD.academic_period_id AND student_mark_type_status_grades.education_grade_id = OLD.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = OLD.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 1 AND institution_subject_students.education_grade_id = OLD.education_grade_id AND institution_subject_students.institution_id = OLD.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ), 0 ) < summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.present_male_count + 1 ELSE summary_area_institution_grade_attendances.present_male_count END ,summary_area_institution_grade_attendances.absent_female_count = CASE WHEN OLD.absence_type_id != 3 AND COALESCE ( ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = OLD.academic_period_id AND student_mark_type_status_grades.education_grade_id = OLD.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = OLD.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 2 AND institution_subject_students.education_grade_id = OLD.education_grade_id AND institution_subject_students.institution_id = OLD.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ), 0 ) < summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.absent_female_count - 1 ELSE summary_area_institution_grade_attendances.absent_female_count END ,summary_area_institution_grade_attendances.absent_male_count = CASE WHEN OLD.absence_type_id != 3 AND COALESCE ( ( SELECT COUNT(*) absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = OLD.academic_period_id AND student_mark_type_status_grades.education_grade_id = OLD.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = OLD.academic_period_id AND institution_subject_students.student_status_id = 1 AND security_users.gender_id = 1 AND institution_subject_students.education_grade_id = OLD.education_grade_id AND institution_subject_students.institution_id = OLD.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id != 3 AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ), 0 ) < summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.absent_male_count - 1 ELSE summary_area_institution_grade_attendances.absent_male_count END ,late_female_count = CASE WHEN OLD.absence_type_id = 3 AND COALESCE ( ( SELECT COUNT(DISTINCT(institution_student_absence_details.student_id)) late_counter FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id WHERE institution_student_absence_details.academic_period_id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id = OLD.absence_type_id AND security_users.gender_id = 2 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ), 0 ) < late_female_count THEN late_female_count - 1 ELSE late_female_count END ,late_male_count = CASE WHEN OLD.absence_type_id = 3 AND COALESCE ( ( SELECT COUNT(DISTINCT(institution_student_absence_details.student_id)) late_counter FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id WHERE institution_student_absence_details.academic_period_id = OLD.academic_period_id AND institution_student_absence_details.date = OLD.date AND institution_student_absence_details.absence_type_id = OLD.absence_type_id AND security_users.gender_id = 1 AND institution_student_absence_details.education_grade_id = OLD.education_grade_id AND institution_student_absence_details.institution_id = OLD.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ), 0 ) < late_male_count THEN late_male_count - 1 ELSE late_male_count END WHERE summary_area_institution_grade_attendances.attendance_date = OLD.date AND summary_area_institution_grade_attendances.institution_id = OLD.institution_id AND summary_area_institution_grade_attendances.education_grade_id = OLD.education_grade_id AND summary_area_institution_grade_attendances.academic_period_id = OLD.academic_period_id; END; ');

        // Create TRIGGER to count absences and late upon UPDATE operation in institution_student_absence_details
        $this->execute('CREATE TRIGGER trigger_institution_student_absence_details_update AFTER UPDATE ON institution_student_absence_details FOR EACH ROW BEGIN UPDATE summary_area_institution_grade_attendances INNER JOIN( SELECT IFNULL(female_absence_counter, 0) female_absence_counter ,IFNULL(male_absence_counter, 0) male_absence_counter ,IFNULL(female_late_counter, 0) female_late_counter ,IFNULL(male_late_counter, 0) male_late_counter FROM ( SELECT "dummy" AS dummy) dummy_source LEFT JOIN ( SELECT attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ,attend_info.absence_date ,COUNT(DISTINCT(CASE WHEN attend_info.gender_id = 2 THEN attend_info.student_id END)) female_absence_counter ,COUNT(DISTINCT(CASE WHEN attend_info.gender_id = 1 THEN attend_info.student_id END)) male_absence_counter FROM ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date absence_date ,institution_student_absence_details.subject_id ,period_counter.attendance_per_day period_attendance_per_day ,subject_counter.subjects_taken ,attendance_type.value ,security_users.gender_id ,institution_student_absence_details.absence_type_id FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id INNER JOIN academic_periods ON academic_periods.id = institution_student_absence_details.academic_period_id INNER JOIN ( SELECT student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ,student_attendance_mark_types.attendance_per_day FROM student_mark_type_status_grades INNER JOIN student_mark_type_statuses ON student_mark_type_statuses.id = student_mark_type_status_grades.student_mark_type_status_id INNER JOIN student_attendance_mark_types ON student_attendance_mark_types.id = student_mark_type_statuses.student_attendance_mark_type_id INNER JOIN academic_periods ON academic_periods.id = student_mark_type_statuses.academic_period_id WHERE academic_periods.id = NEW.academic_period_id AND student_mark_type_status_grades.education_grade_id = NEW.education_grade_id GROUP BY student_mark_type_status_grades.education_grade_id ,student_mark_type_statuses.academic_period_id ) period_counter ON period_counter.education_grade_id = institution_student_absence_details.education_grade_id AND period_counter.academic_period_id = institution_student_absence_details.academic_period_id LEFT JOIN ( SELECT institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ,COUNT(DISTINCT(institution_subject_students.education_subject_id)) subjects_taken FROM institution_subject_students INNER JOIN academic_periods ON academic_periods.id = institution_subject_students.academic_period_id INNER JOIN security_users ON security_users.id = institution_subject_students.student_id WHERE academic_periods.id = NEW.academic_period_id AND institution_subject_students.student_status_id = 1 AND institution_subject_students.education_grade_id = NEW.education_grade_id AND institution_subject_students.institution_id = NEW.institution_id GROUP BY institution_subject_students.academic_period_id ,institution_subject_students.institution_id ,institution_subject_students.education_grade_id ,institution_subject_students.institution_class_id ,institution_subject_students.student_id ) subject_counter ON subject_counter.academic_period_id = institution_student_absence_details.academic_period_id AND subject_counter.institution_id = institution_student_absence_details.institution_id AND subject_counter.education_grade_id = institution_student_absence_details.education_grade_id AND subject_counter.institution_class_id = institution_student_absence_details.institution_class_id AND subject_counter.student_id = institution_student_absence_details.student_id INNER JOIN ( SELECT config_items.value FROM config_items WHERE config_items.code LIKE "calculate_daily_attendance" ) attendance_type WHERE academic_periods.id = NEW.academic_period_id AND institution_student_absence_details.absence_type_id != 3 AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.institution_class_id ,institution_student_absence_details.student_id ,institution_student_absence_details.date HAVING CASE WHEN attendance_type.value = 1 THEN COUNT(*) >= 1 ELSE CASE WHEN institution_student_absence_details.subject_id = 0 THEN COUNT(*) >= period_counter.attendance_per_day ELSE COUNT(*) >= IFNULL(subject_counter.subjects_taken, 0) END END ) attend_info GROUP BY attend_info.academic_period_id ,attend_info.institution_id ,attend_info.education_grade_id ) absence_calc ON 1=1 LEFT JOIN ( SELECT institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ,institution_student_absence_details.date late_date ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 2 THEN institution_student_absence_details.student_id END)) female_late_counter ,COUNT(DISTINCT(CASE WHEN security_users.gender_id = 1 THEN institution_student_absence_details.student_id END)) male_late_counter FROM institution_student_absence_details INNER JOIN security_users ON security_users.id = institution_student_absence_details.student_id WHERE institution_student_absence_details.academic_period_id = NEW.academic_period_id AND institution_student_absence_details.absence_type_id = 3 AND institution_student_absence_details.date = NEW.date AND institution_student_absence_details.education_grade_id = NEW.education_grade_id AND institution_student_absence_details.institution_id = NEW.institution_id GROUP BY institution_student_absence_details.academic_period_id ,institution_student_absence_details.institution_id ,institution_student_absence_details.education_grade_id ) late_calc ON 1=1 ) attendance_info SET summary_area_institution_grade_attendances.present_female_count = CASE WHEN attendance_info.female_absence_counter > summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.present_female_count - 1 WHEN attendance_info.female_absence_counter < summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.present_female_count + 1 ELSE summary_area_institution_grade_attendances.present_female_count END ,summary_area_institution_grade_attendances.present_male_count = CASE WHEN attendance_info.male_absence_counter > summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.present_male_count - 1 WHEN attendance_info.male_absence_counter < summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.present_male_count + 1 ELSE summary_area_institution_grade_attendances.present_male_count END ,summary_area_institution_grade_attendances.absent_female_count = CASE WHEN attendance_info.female_absence_counter > summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.absent_female_count + 1 WHEN attendance_info.female_absence_counter < summary_area_institution_grade_attendances.absent_female_count THEN summary_area_institution_grade_attendances.absent_female_count - 1 ELSE summary_area_institution_grade_attendances.absent_female_count END ,summary_area_institution_grade_attendances.absent_male_count = CASE WHEN attendance_info.male_absence_counter > summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.absent_male_count + 1 WHEN attendance_info.male_absence_counter < summary_area_institution_grade_attendances.absent_male_count THEN summary_area_institution_grade_attendances.absent_male_count - 1 ELSE summary_area_institution_grade_attendances.absent_male_count END ,summary_area_institution_grade_attendances.late_female_count = CASE WHEN attendance_info.female_late_counter > summary_area_institution_grade_attendances.late_female_count THEN summary_area_institution_grade_attendances.late_female_count + 1 WHEN attendance_info.female_late_counter < summary_area_institution_grade_attendances.late_female_count THEN summary_area_institution_grade_attendances.late_female_count - 1 ELSE summary_area_institution_grade_attendances.late_female_count END ,summary_area_institution_grade_attendances.late_male_count = CASE WHEN attendance_info.male_late_counter > summary_area_institution_grade_attendances.late_male_count THEN summary_area_institution_grade_attendances.late_male_count + 1 WHEN attendance_info.male_late_counter < summary_area_institution_grade_attendances.late_male_count THEN summary_area_institution_grade_attendances.late_male_count - 1 ELSE summary_area_institution_grade_attendances.late_male_count END WHERE summary_area_institution_grade_attendances.attendance_date = NEW.date AND summary_area_institution_grade_attendances.institution_id = NEW.institution_id AND summary_area_institution_grade_attendances.education_grade_id = NEW.education_grade_id AND summary_area_institution_grade_attendances.academic_period_id = NEW.academic_period_id; END; ');

        // Create TRIGGER to count totals upon UPDATE operation in summary_area_institution_grade_attendances
        $this->execute('CREATE TRIGGER trigger_summary_area_institution_grade_attendances_update BEFORE UPDATE ON summary_area_institution_grade_attendances FOR EACH ROW BEGIN SET NEW.present_total_count = NEW.present_female_count + NEW.present_male_count, NEW.absent_total_count = NEW.absent_female_count + NEW.absent_male_count, NEW.late_total_count = NEW.late_female_count + NEW.late_male_count; END; ');

    }
         
    // rollback
    public function down()
    {
        // Restore table
        $this->execute('DROP TABLE IF EXISTS `report_queries`');
        $this->execute('RENAME TABLE `zz_7940_report_queries` TO `report_queries`');

        // Restore table
        $this->execute('DROP TABLE IF EXISTS `summary_area_institution_grade_attendances`');
        $this->execute('RENAME TABLE `zz_7940_summary_area_institution_grade_attendances` TO `summary_area_institution_grade_attendances`');

    }
}
?>
